﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffNotInTeam]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffNotInTeam];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffNotInTeam] 
    @TeamID int
/*

============================================================
功能:    得到所有不在@TeamID组中的人员
参数:
    @TeamID int        :    组（类）ID
============================================================

*/
AS
BEGIN
SET NOCOUNT ON

Select
        a.Staff_ID,
        a.Staff_Name,
        a.Realname,
        b.Position_Name
    From
        (
            select * 
            from dbo.uds_staff
            Where staff_id not in (select staff_id from dbo.uds_staff_in_Team where Team_id = @Teamid) and dimission = 0
        ) a,
        dbo.UDS_Position b,
        dbo.UDS_Staff_In_Position c
    Where
        c.Position_id = b.Position_id
        and a.staff_id = c.staff_id
    order by
        a.staff_name

END
GO